package com.zlm.common.util;

import com.zlm.common.dto.ExcelCellDto;
import com.zlm.common.enums.WorkBookTypeEnum;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;

/**
 * Excel读写工具类
 * @Author: xhj
 * @Date: 2019/5/24
 */
public class ExcelUtils implements Closeable {
    /**
     * 样式
     */
    private int templateLines = 0;
    private int testLine = 0;
    private CellStyle[] testLineStyle = null;

    /**
     * excel工作薄
     */
    private Workbook wb = null;
    private Sheet sheet = null;

    /**
     * 列数
     */
    private int columnNum = -1;

    /**
     * 字段名集合（按列顺序）
     */
    private String[] keyList;

    private final WorkBookTypeEnum workbookType;

    public ExcelUtils(String[] keyList) {
        // 创建excel文件
        this.wb = new HSSFWorkbook();
        // 创建工作薄
        this.sheet = wb.createSheet();
        this.keyList = keyList;
        // 初始化总列数
        this.columnNum = keyList.length;
        workbookType = WorkBookTypeEnum.HSSF;
    }

    public ExcelUtils(String[] keyList, WorkBookTypeEnum type) {
        // 创建excel文件
        this.wb = new HSSFWorkbook();

        switch (type) {
            case HSSF:
                this.wb = new HSSFWorkbook();
                break;
            case SXSSF:
                this.wb = new SXSSFWorkbook();
                break;
            case XSSF:
                this.wb = new XSSFWorkbook();
                break;
            default:
                throw new RuntimeException("unsupport workbook type");
        }

        // 创建工作薄
        this.sheet = wb.createSheet();
        this.keyList = keyList;
        // 初始化总列数
        this.columnNum = keyList.length;
        workbookType = type;
    }

    /**
     * 渲染表头（根据模板）
     *
     * @param templatePath
     *            模板路径
     * @param replace
     *            要替换的参数map
     * @param testLine
     *            测试行数
     * @throws IOException
     */
    public void renderTitle(String templatePath, Map<String, String> replace, int testLine) throws IOException {
        // 初始化测试行数
        this.testLine = testLine;
        InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(templatePath);
        switch (workbookType) {
            case HSSF:
                // 读取模板文件
                POIFSFileSystem fs = new POIFSFileSystem(in);
                this.wb = new HSSFWorkbook(fs);
                break;
            case SXSSF:
            case XSSF:
                wb = new XSSFWorkbook(in);
                break;
            default:
                throw new RuntimeException("unsupport workbook type");
        }

        sheet = wb.getSheetAt(0);

        // 取得总行数
        int maxRow = sheet.getLastRowNum();
        // 初始化模板行数（测试行未计入）
        templateLines = maxRow + 1 - this.testLine;

        // 获取测试行样式
        if (this.testLine > 0) {
            Row rowTestLine = sheet.getRow(templateLines);
            CellStyle[] styleArrTemp = new CellStyle[columnNum];
            for (int i = 0; i < columnNum; i++) {
                Cell cellN = rowTestLine.getCell(i);
                CellStyle StyleN = wb.createCellStyle();
                if (cellN != null) {
                    StyleN = cellN.getCellStyle();
                }

                styleArrTemp[i] = StyleN;
            }
            testLineStyle = styleArrTemp;
        }

        // 删除测试行
        for (int t = templateLines; t <= maxRow; t++) {
            sheet.removeRow(sheet.getRow(t));
        }

        // 遍历模板，替换参数
        for (int n = 0; n < templateLines; n++) {
            Row rowN = sheet.getRow(n);
            for (int i = 0; i < columnNum; i++) {
                Cell cellN = rowN.getCell(i);
                if (cellN != null) {
                    String v = cellN.getStringCellValue();
                    // 替换参数
                    v = replaceParam(v, replace);
                    cellN.setCellValue(v);
                }
            }
        }

        if (workbookType.equals(WorkBookTypeEnum.SXSSF)) {
            wb = new SXSSFWorkbook((XSSFWorkbook) wb);
            sheet = wb.getSheetAt(0);
        }
    }

    public void renderTitleEx(List<List<ExcelCellDto>> title) {
        renderTitle(title, null, null);
    }

    public void renderTitleEx(List<List<ExcelCellDto>> title, int[] columnWidth) {
        renderTitle(title, columnWidth, null);
    }

    /**
     * 渲染表头（通过传递过来的参数值）
     *
     * @param title
     *            表头数据
     * @param columnWidth
     *            列宽（数组）
     * @param topName
     *            第一行数据
     */
    public void renderTitle(List<List<ExcelCellDto>> title, int[] columnWidth, String topName) {
        // 设置每一列的宽度
        if (columnWidth != null && columnWidth.length > 0) {
            for (int i = 0; i < columnWidth.length; i++) {
                sheet.setColumnWidth(i, columnWidth[i]);
            }
        }

        // 行号
        int row = templateLines;
        if (!StringUtils.isEmpty(topName)) {
            // 渲染第一行
            Map<String, Integer> rowCol = renderTopRow(topName);
            if (rowCol.containsKey(ExcelCellDto.ROWSPAN)) {
                row = rowCol.get(ExcelCellDto.ROWSPAN);
            }
        }

        // (key=>value)记录第key列的可以写入数据的下一行行号value
        Map<Integer, Integer> nextRow = new HashMap<>();

        if (title != null && title.size() > 0) {
            // 重置模板行数
            templateLines = templateLines + title.size();

            for (List<ExcelCellDto> r : title) {
                nextRow = renderRow(r, nextRow, row);
                row++;
            }
        }
    }

    /**
     * 渲染第一行（固定样式）
     *
     * @param topName
     * @return
     */
    public Map<String, Integer> renderTopRow(String topName) {
        ExcelCellDto cell = new ExcelCellDto();
        cell.setValue(topName)
                .setColspan(columnNum)
                .setFontSize((short) 20)
                .setHorizontalAlign("center")
                .setBold(true)
                .setFontFamily("宋体");

        return renderCell(cell, sheet.createRow(0), 0);
    }

    /**
     * 渲染单行数据（不带样式）
     *
     * @param rowMap
     *            内容（不带样式，纯内容）
     * @param rowNum
     *            行号
     */
    public void renderRow(Map<String, Object> rowMap, int rowNum) {
        Row rowN = sheet.createRow(rowNum + templateLines);
        for (int i = 0; i < columnNum; i++) {
            Object obj = rowMap.get(keyList[i]);
            if ("".equals(obj)) {
                obj = null;
            }

            renderCell(obj, rowN, i);
        }
    }

    /**
     * 渲染单行数据（包含样式）
     *
     * @param row
     *            一行内每个单元格数据集合（包含样式）
     * @param nextRow
     *            (key=>value)记录第key列的可以写入数据的下一行行号value
     * @param row
     *            行号
     * @return
     */
    public Map<Integer, Integer> renderRow(List<ExcelCellDto> row, Map<Integer, Integer> nextRow, int rowNum) {
        if (row != null && row.size() > 0) {
            // 列数
            int col = 0;
            Row rowN = sheet.getRow(rowNum);
            if (rowN == null) {
                // 创建一行
                rowN = sheet.createRow(rowNum);
            }

            for (ExcelCellDto cell : row) {
                col = checkCell(nextRow, rowNum, col);
                // 记录当前列数
                int thisCol = col;

                // 渲染单元格
                Map<String, Integer> rowColN = renderCell(cell, rowN, col);
                // 下一列的列数
                col = col + rowColN.get(ExcelCellDto.COLSPAN);
                for (int i = thisCol; i < col; i++) {
                    nextRow.put(i, rowNum + rowColN.get(ExcelCellDto.ROWSPAN));
                }
            }
        }

        return nextRow;
    }

    /**
     * 渲染单元格（本身不带样式，如果有测试样式，则按测试样式渲染）
     *
     * @param obj
     *            单元格内容
     * @param rowN
     *            所在行
     * @param col
     *            列数
     */
    public void renderCell(Object obj, Row rowN, int col) {
        // 创建一个单元格
        Cell cellN = rowN.createCell(col);
        // 中文处理
        // cellN.setCellType(HSSFCell.ENCODING_UTF_16);

        // 如果有测试行样式，则给单元格设置样式
        if (testLineStyle != null && testLineStyle.length >= col) {
            cellN.setCellStyle(testLineStyle[col]);
        }

        if (obj instanceof String) {
            cellN.setCellValue((String) obj);
        }
        else if (obj instanceof Double) {
            cellN.setCellValue((Double) obj);
        }
        else if (obj instanceof Float) {
            cellN.setCellValue((Float) obj);
        }
        else if (obj instanceof BigDecimal) {
            cellN.setCellValue(((BigDecimal) obj).doubleValue());
        }
        else if (obj instanceof Integer) {
            cellN.setCellValue((Integer) obj);
        }
        else if (obj instanceof Long) {
            cellN.setCellValue((Long) obj);
        }
        else if (obj instanceof Date) {
            cellN.setCellValue((Date) obj);
        }
        else if (obj instanceof Calendar) {
            cellN.setCellValue((Calendar) obj);
        }
        else if (obj instanceof RichTextString) {
            cellN.setCellValue((RichTextString) obj);
        }
        else {

            if (obj == null) {
                cellN.setCellType(CellType.BLANK);
            }
            else {
                cellN.setCellValue(obj.toString());
            }

        }
    }

    /**
     * 渲染单元格
     *
     * @param cell
     *            单元格参数（内容，样式）
     * @param rowN
     *            所在行
     * @param col
     *            列数
     * @return
     */
    public Map<String, Integer> renderCell(ExcelCellDto cell, Row rowN, int col) {
        // 行数
        int row = rowN.getRowNum();

        Cell cellN = rowN.getCell(col);
        if (cellN == null) {
            // 创建一个单元格
            cellN = rowN.createCell(col);
        }
        // 中文处理
        // cellN.setCellType(HSSFCell.ENCODING_UTF_16);

        /*--处理字体样式-----------------------------------*/
        // 字体对象
        Font fontN = wb.createFont();
        // 字体颜色
        if (cell.getColor() != null) {
            fontN.setColor(cell.getColor());
        }
        // 加粗
        if (cell.getBold() != null) {
            fontN.setBold(cell.getBold());
        }
        // 字体大小
        if (cell.getFontSize() != null) {
            fontN.setFontHeightInPoints(cell.getFontSize());
        }
        // 字体
        if (cell.getFontFamily() != null) {
            fontN.setFontName(cell.getFontFamily());
        }

        // 样式对象
        CellStyle styleN = wb.createCellStyle();
        styleN.setFont(fontN);
        // 水平对齐
        if (cell.getHorizontalAlign() != null) {
            styleN.setAlignment(getHorizontalAlign(cell.getHorizontalAlign()));
        }
        // 垂直对齐
        if (cell.getVerticalAlign() != null) {
            styleN.setVerticalAlignment(getVerticalAlign(cell.getVerticalAlign()));
        }

        // 单元格背景颜色
        if (cell.getBgColor() != null) {
            styleN.setFillForegroundColor(cell.getBgColor());
            styleN.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        // 是否自动换行
        if (cell.getWrap() != null) {
            styleN.setWrapText(cell.getWrap());
        }

        // 边框
        if (cell.getBorder() != null) {
            styleN.setBorderTop(cell.getBorder());
            styleN.setBorderRight(cell.getBorder());
            styleN.setBorderBottom(cell.getBorder());
            styleN.setBorderLeft(cell.getBorder());
        }
        /*--处理字体样式-----------------------------------*/

        /*--处理内容格式-----------------------------------*/
        if (cell.getIsPercent() != null && cell.getIsPercent()) {

            if (workbookType.equals(WorkBookTypeEnum.HSSF)) {
                // 设置单元格内容为百分数格式
                styleN.setDataFormat(HSSFDataFormat.getBuiltinFormat(ExcelCellDto.PERCENT_FORMAT));
            }
            else if (workbookType.equals(WorkBookTypeEnum.XSSF) || workbookType.equals(WorkBookTypeEnum.SXSSF)) {
                XSSFDataFormat fmt = (XSSFDataFormat) wb.createDataFormat();
                styleN.setDataFormat(fmt.getFormat(ExcelCellDto.PERCENT_FORMAT));
            }

            cellN.setCellStyle(styleN);
            cellN.setCellValue(Double.parseDouble(cell.getValue()));
        }
        else {
            cellN.setCellStyle(styleN);
            cellN.setCellValue(cell.getValue());
        }
        /*--处理内容格式-----------------------------------*/

        /*--合并单元格-----------------------------------*/
        int colspan = 1;
        int rowspan = 1;
        if (cell.getColspan() != null) {
            colspan = cell.getColspan();
        }
        if (cell.getRowspan() != null) {
            rowspan = cell.getRowspan();
        }

        int firstRow = row;
        int lastRow = row + rowspan - 1;
        int firstCol = col;
        int lastCol = col + colspan - 1;

        if (lastRow > firstRow || lastCol > firstCol) {
            CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            // 合并单元格
            sheet.addMergedRegion(region);

            // 设置边框
            if (cell.getBorder() != null) {
                RegionUtil.setBorderBottom(cell.getBorder(), region, sheet);
                RegionUtil.setBorderLeft(cell.getBorder(), region, sheet);
                RegionUtil.setBorderRight(cell.getBorder(), region, sheet);
                RegionUtil.setBorderTop(cell.getBorder(), region, sheet);
            }
        }
        /*--合并单元格-----------------------------------*/

        Map<String, Integer> returnMap = new HashMap<>();
        returnMap.put(ExcelCellDto.COLSPAN, colspan);
        returnMap.put(ExcelCellDto.ROWSPAN, rowspan);

        return returnMap;
    }

    public byte[] outputByte() throws IOException {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        IOUtils.closeQuietly(bos);
        IOUtils.closeQuietly(wb);

        return bos.toByteArray();
    }

    @Override
    public void close() {
        IOUtils.closeQuietly(wb);
    }

    /**
     * 替换参数
     *
     * @param v
     * @param replace
     * @return
     */
    public String replaceParam(String v, Map<String, String> replace) {
        if (!"".equals(v) && replace != null && !replace.isEmpty()) {
            for (String key : replace.keySet()) {
                v = v.replace(key, replace.get(key));
            }
        }
        return v;
    }

    /**
     * 校验单元格是否有效，返回下一个有效单元格的列数
     *
     * @param nextRow
     * @param row
     * @param col
     * @return
     */
    public int checkCell(Map<Integer, Integer> nextRow, int row, int col) {
        if (nextRow.containsKey(col) && row < nextRow.get(col)) {
            // 上一行该列的rowspan大于1，跳过该列
            col++;
            return checkCell(nextRow, row, col);
        }
        else {
            return col;
        }
    }

    /**
     * align转换
     *
     * @param a
     * @return
     */
    public HorizontalAlignment getHorizontalAlign(String a) {
        switch (a) {
            case "center":
                return HorizontalAlignment.CENTER;
            case "right":
                return HorizontalAlignment.RIGHT;
            case "left":
                return HorizontalAlignment.LEFT;
            default:
                return HorizontalAlignment.LEFT;
        }
    }

    /**
     * align转换
     *
     * @param a
     * @return
     */
    public VerticalAlignment getVerticalAlign(String a) {
        switch (a) {
            case "middle":
                return VerticalAlignment.CENTER;
            case "top":
                return VerticalAlignment.TOP;
            case "bottom":
                return VerticalAlignment.BOTTOM;
            default:
                return VerticalAlignment.CENTER;
        }
    }

    /**
     * 文件输出
     *
     * @param fileName
     *            文件名
     * @throws IOException
     */
    public void fileOut(String fileName) throws IOException {
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        IOUtils.closeQuietly(fileOut);
        IOUtils.closeQuietly(wb);
    }
}
